import sys
import ConfigParser
import MySQLdb
import cPickle
sys.path.append('..')
from ClinicHistory import *
from Person import *
from Soap import *
from Sincronizacion import *

from M2Crypto import SSL as SSL_LIB

HOST = '127.0.0.1'
PORT = 5050
BACKLOG = 5
BUFF_SIZE = 1024

SSL_PATH = '../certificados/'

SSL_VERSION = 'sslv3'
SSL_CERT_SER = 'certificado-servidor.pem'
SSL_KEY_SER = 'claveprivada.pem'
SSL_CERT_AUTH = 'CAXplotacert.pem'

BD_CONFIG = '../settings_server.ini'

class SSL:
	""" Objeto que me proporciona
		la conexion segura con SSL """

	def __init__(self):
		self.ssl_path = SSL_PATH
		self.ssl_version = SSL_VERSION
		self.ssl_cert_ser = SSL_CERT_SER
		self.ssl_key_ser = SSL_KEY_SER
		self.ssl_cert_auth = SSL_CERT_AUTH

	def context(self):
		con = SSL_LIB.Context(self.ssl_version)
		con.load_cert(certfile = self.ssl_path + self.ssl_cert_ser,
				keyfile = self.ssl_path + self.ssl_key_ser)
		con.load_verify_locations(self.ssl_path + self.ssl_cert_auth)
		con.set_verify(SSL_LIB.verify_peer | 
				SSL_LIB.verify_fail_if_no_peer_cert, depth = 9)
		return con


class Server:
	""" Objeto que me proporciona
		la conexion con el Cliente """

	def __init__(self, con):
		self.host = HOST
		self.port = PORT
		self.backlog = BACKLOG
		self.buff_size = BUFF_SIZE
		self.con = con

	def conect(self):
		bindsocket = SSL_LIB.Connection(self.con)
		bindsocket.bind((self.host,self.port))
		bindsocket.listen(self.backlog)
		print "Esperando por conexiones"

		(connectsocket, fromaddress) = bindsocket.accept()
		if connectsocket.get_peer_cert() is not None:
			print "Servidor: Certificado del Cliente Correcto"
			self.connectsocket = connectsocket
			self.bindsocket = bindsocket
		else:
			print "Server: No se pudo validar el Certificado" 
			sys.exit()

	def recv(self):
		data = self.connectsocket.read()
		len_info = 0
		if len(data) != 0:
			len_info = cPickle.loads(data)
		list = []
		while len_info > 0:
			data = self.connectsocket.read()
			list.append(cPickle.loads(data))
			len_info -= 1
		return list

	def recv_ex(self):
		data = self.connectsocket.read()
		len_info = 0
		if len(data) != 0:
			len_info = cPickle.loads(data)
		list = []
		while len_info > 0:
			list_temp = []
			tam = self.connectsocket.read()
			tam = cPickle.loads(tam)
			while tam > 0:
				data = self.connectsocket.read()
				list_temp.append(cPickle.loads(data))
				tam -= 1
			list.append(tuple(list_temp))
			len_info -= 1
		return list

	def send(self, data):
		len_data = cPickle.dumps(len(data))
		lenn = len(data)
		self.connectsocket.send(len_data)
		i = 0 
		while lenn > 0:
			self.connectsocket.send(cPickle.dumps(data[i]))                          
			i += 1
			lenn -= 1

	def recv_message(self):
		data = self.connectsocket.read()
		return data

	def send_message(self,data):
		self.connectsocket.send(data)

	def close(self):
		self.connectsocket.close()
		self.bindsocket.close()

class BD:
	""" Conexion con la Base de Datos """

	def __init__(self):
		self.bd_config = BD_CONFIG
		self.bd_names = self.getting_names()

	def getting_names(self):
		cp = ConfigParser.ConfigParser()
		cp.read(self.bd_config)
		return cp

	def conect(self):
		try:
			self.db = MySQLdb.connect(
					host=self.bd_names.get('DATABASE','DB_HOSTNAME'),
					user=self.bd_names.get('DATABASE','DB_USERNAME'),
					passwd=self.bd_names.get('DATABASE','DB_PASSWORD'),
					db=self.bd_names.get('DATABASE','DB_NAME'))
		except MySQLdb.Error, e:
			self.error_message(e)

	def get_rows_diff(self,location):
		""" Obtiene los registros
			que tienen versiones clientes
			distintas a cero """
		try:
			c = self.db.cursor()
			c.execute("""SELECT * 
					FROM """+ self.bd_names.get('DATABASE','DB_TABLE') +
					""" WHERE empadronado=%s and
					version_actual <> version_sincronizacion""", (location,))
			return c.fetchall()
		except MySQLdb.Error, e:
			self.error_message(e)

	def get_real_pid(self, cid):
		try:
			c = self.db.cursor()
			c.execute("""SELECT pid FROM care_person WHERE nat_id_nr=%s""",
					(cid,))
			return int(c.fetchone()[0])
		except MySQLdb.Error, e:
			self.error_message(e)

	def update_sync(self, data, server_name):
		try:
			if len(data) > 0:
				c = self.db.cursor()
				for i in data:
					if server_name == i.get_empadronado():
						value = i.get_version_actual()
						ver_actual = 'No' 
						ver_sinco = 'No'
					else:
						value = i.get_version_sincronizacion() 
						ver_actual = i.get_tipo_historia_clinica()
						ver_sinco = i.get_tipo_soap()
					real_pid = self.get_real_pid(i.get_cedula())
					is_exe = c.execute("""UPDATE """ + 
							self.bd_names.get('DATABASE','DB_TABLE') +
							""" SET version_actual=%s, version_sincronizacion=%s,
								tipo_historia_clinica=%s, tipo_soap=%s
								WHERE pid=%s and cedula=%s""",(i.get_version_actual(),
									value,ver_actual,ver_sinco,real_pid,i.get_cedula(),))
					if int(is_exe) == 0:
						c.execute("""INSERT INTO """ +
							self.bd_names.get('DATABASE','DB_TABLE') +
							""" VALUES (%s,%s,%s,%s,%s,%s,%s,%s)""",
							(real_pid,i.get_cedula(),i.get_correlativo(),
							i.get_version_actual(),value,
							i.get_empadronado(),'No','No',))
		except MySQLdb.Error, e:
			self.error_message(e)

	def update_clin_hist(self, data, regs):
		try:
			if len(data) > 0:
				c = self.db.cursor()
				for i in regs:
					real_pid = self.get_real_pid(i.get_cedula())
					c.execute("""DELETE FROM 
							care_historia_datos_historia_clinica 
							WHERE id_paciente=%s""",
							(real_pid,))
				for i in regs:
					list_to_insert = self.get_list_hist(data,i)
					c.executemany("""INSERT INTO 
						care_historia_datos_historia_clinica 
						VALUES (%s,%s,%s,%s,%s)""",list_to_insert)
		except MySQLdb.Error, e:
			self.error_message(e)

	def update_soap(self, data, regs):
		try:
			if len(data) > 0:
				c = self.db.cursor()
				for i in regs:
					real_pid = self.get_real_pid(i.get_cedula())
					c.execute("""DELETE FROM 
						    care_historia_soap	
							WHERE id_paciente=%s """,
							(real_pid),)
				for i in regs:
					list_to_insert = self.get_list_soap(data,i)
					c.executemany("""INSERT INTO 
						care_historia_soap
						VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
						list_to_insert)
		except MySQLdb.Error, e:
			self.error_message(e)

	def update_person(self, data):
		try:
			if len(data) > 0:
				c = self.db.cursor()
				for i in data:
					is_exe = c.execute("""SELECT * FROM care_person
								WHERE nat_id_nr=%s""",(i.get_nat_id_nr(),))
					if int(is_exe) != 1:
						list_to_insert = self.get_list_person(data)
						c.executemany("""INSERT INTO care_person
						VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
							%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
							%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
							%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
							%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
							%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
							%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
							%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
							list_to_insert)
		except MySQLdb.Error, e:
			self.error_message(e)

	def get_regs_info_send_person(self, regs):
		try:
			c = self.db.cursor()
			string = self.preparing_string_person(regs)
			if string != "nada":
				c.execute(string,)
				return c.fetchall()
			else:
				return ()
		except MySQLdb.Error, e:
			self.error_message(e)

	def get_regs_info_send_hist(self, regs):
		try:
			c = self.db.cursor()
			string = self.preparing_string_hist(regs)
			if string != "nada":
				c.execute(string,)
				return c.fetchall()
			else:
				return ()
		except MySQLdb.Error, e:
			self.error_message(e)

	def get_regs_info_send_soap(self, regs):
		try:
			c = self.db.cursor()
			string = self.preparing_string_soap(regs)
			if string != "nada":
				c.execute(string,)
				return c.fetchall()
			else:
				return ()
		except MySQLdb.Error, e:
			self.error_message(e)

	def get_list_hist(self, info, reg):
		list = []
		for i in info:
			real_pid = self.get_real_pid(reg.get_cedula())
			if i.get_id_paciente() == reg.get_pid():
				list.append((i.get_id_campo(),i.get_valor(),
					real_pid,i.get_id_correlativo(),
					i.get_ficha()))
		return list

	def get_list_soap(self, info, reg):
		list = []
		for i in info:
			real_pid = self.get_real_pid(reg.get_cedula())
			if i.get_id_paciente() == reg.get_pid():
				list.append((0,i.get_fecha(),i.get_nombre_medico(),
					i.get_matricula_medico(),real_pid,
					i.get_clasificacion_medico(),i.get_subjetivo(),
					i.get_objetivo(),i.get_apreciacion(),i.get_planTPR(),
					i.get_tratamiento(),i.get_tipo(),i.get_referido()))
		return list

	def get_list_person(self, info):
		list = []
		for i in info:
			list.append((0,i.get_date_reg(),i.get_name_first(),
				i.get_name_2(),i.get_name_3(),i.get_name_middle(),
				i.get_name_last(),i.get_name_maiden(),i.get_name_others(),
				i.get_date_birth(),i.get_blood_group(),i.get_addr_str(),
				i.get_addr_str_nr(),i.get_addr_zip(),i.get_addr_citytown_nr(),
				i.get_addr_is_valid(),i.get_citizenship(),i.get_phone_1_code(),
				i.get_phone_1_nr(),i.get_phone_2_code(),i.get_phone_2_nr(),
				i.get_cellphone_1_nr(),i.get_cellphone_2_nr(),i.get_fax(),
				i.get_email(),i.get_civil_status(),i.get_sex(),i.get_title(),
				i.get_photo(),i.get_photo_filename(),i.get_ethnic_orig(),
				i.get_org_id(),i.get_sss_nr(),i.get_nat_id_nr(),i.get_religion(),
				i.get_mother_pid(),i.get_father_pid(),i.get_contact_person(),
				i.get_contact_pid(),i.get_contact_relation(),i.get_death_date(),
				i.get_death_encounter_nr(),i.get_death_cause(),i.get_death_cause_code(),
				i.get_date_updade(),i.get_status(),i.get_history(),
				i.get_modify_id(),i.get_modify_time(),i.get_create_id(),
				i.get_create_time(),i.get_relative_name_first(),
				i.get_relative_name_last(),i.get_relative_phone(),
				i.get_birth_country(),i.get_birth_entity(),i.get_birth_town(),
				i.get_birth_city(),i.get_illiterate(),i.get_educational_level(),
				i.get_educational_plan(),i.get_years_approved(),i.get_profession(),
				i.get_occupation(),i.get_sso(),i.get_addr_citytown_name(),
				i.get_addr_locality(),i.get_addr_entity(),i.get_addr_town(),
				i.get_addr_sector(),i.get_addr_house(),i.get_addr_floor(),
				i.get_addr_reference(),i.get_addr_time(),i.get_name_mother(),
				i.get_name_father(),i.get_comments(),i.get_imaging(),
				i.get_cod_estado(),i.get_numero()))
		return list

	def preparing_string_person(self, opts):
		string = """SELECT * FROM care_person WHERE"""
		for i in range(len(opts)):
			if (len(opts) - 1) == i:
				string += ' nat_id_nr = ' + '"' + opts[i].get_cedula() + '"'
				break
			string += ' nat_id_nr = ' + '"' + opts[i].get_cedula() + '"' + ' or'
		return string

	def preparing_string_hist(self, opts):
		string = """SELECT id_campo,valor,id_paciente,id_correlativo,ficha
			FROM care_historia_datos_historia_clinica WHERE"""
		#if not sp:
		r = count_hist(opts)
		if not r > 0:
			string = "nada"
		#else:
		#	r = len(opts)
		for i in range(r):
			if (r - 1) == i:
				string += ' (id_paciente=' + str(opts[i].get_pid()) + ')'
				break
			string += ' (id_paciente=' + str(opts[i].get_pid()) + ') or'
		return string

	def preparing_string_soap(self, opts):
		string = """SELECT id,fecha,nombre_medico,
			matricula_medico,id_paciente,clasificacion_medico,
			subjetivo,objetivo,apreciacion,planTPR,tratamiento,
	        tipo,referido
	        FROM care_historia_soap WHERE"""
		#if not sp:
		r = count_soap(opts)
		if not r > 0:
			string = "nada"
		#else:
		#	r = len(opts)
		for i in range(r):
			if (r - 1) == i:
				string += ' (id_paciente=' + str(opts[i].get_pid()) + ')'
				break
			string += ' (id_paciente=' + str(opts[i].get_pid()) + ') or'
		return string

	def error_message(self, error):
		print "Cliente: Error conexion con la Base de Datos"
		print "Error %d: %s" % (error.args[0], error.args[1])
		sys.exit()

	def close(self):
		self.db.close()

def count_hist(opts):
	c = 0
	for i in opts:
		if i.get_tipo_historia_clinica() == 'Si':
			c +=1
	return c

def count_soap(opts):
	c = 0
	for i in opts:
		if i.get_tipo_soap() == 'Si':
			c +=1
	return c

def get_regs_conflict(server_regs, my_regs):
	lista = []
	if len(my_regs) == 0 or len(server_regs) == 0:
		return lista
	for i in server_regs:
		for j in my_regs:
			if (i.get_cedula() == j.get_cedula() and
					i.get_version_actual() != j.get_version_actual()):
				lista.append(j)
	return lista

def generators_sync(options):
	list = []
	for i in options:
		list.append(Sincronizacion(int(i[0]),i[1],
				int(i[2]),int(i[3]),int(i[4]),i[5],i[6],i[7]))
	return list

def generators_clin_hist(options):
	list = []
	for i in options:
		list.append(ClinicHistory(i[0],i[1],int(i[2]),
			int(i[3]),i[4]))
	return list

def generators_soap(options):
	list = []
	for i in options:
		list.append(Soap(int(i[0]),i[1],i[2],int(i[3]),int(i[4]),
	        i[5],i[6],i[7],i[8],i[9],i[10],i[11],i[12]))
	return list

def generators_person(options):
	list = []
	for i in options:
		list.append(Person(
			int(i[0]),i[1],i[2],i[3],i[4],i[5],
			i[6],i[7],i[8],i[9],i[10],
			i[11],i[12],i[13],int(i[14]),int(i[15]),
			i[16],i[17],i[18],i[19],i[20],
			i[21],i[22],i[23],i[24],i[25],
			i[26],i[27],i[28],i[29],i[30],
			i[31],i[32],i[33],i[34],int(i[35]),
			int(i[36]),i[37],int(i[38]),i[39],i[40],
			int(i[41]),i[42],i[43],i[44],i[45],
			i[46],i[47],i[48],i[49],i[50],
			i[51],i[52],i[53],i[54],i[55],
			i[56],i[57],i[58],i[59],i[60],
			i[61],i[62],i[63],i[64],i[65],
			i[66],i[67],i[68],i[69],i[70],
			i[71],i[72],i[73],i[74],i[75],
			i[76],i[77],i[78],int(i[79])))
	return list

def gen_person(i):
	return [i.get_pid(),i.get_date_reg(),i.get_name_first(),
				i.get_name_2(),i.get_name_3(),i.get_name_middle(),
				i.get_name_last(),i.get_name_maiden(),i.get_name_others(),
				i.get_date_birth(),i.get_blood_group(),i.get_addr_str(),
				i.get_addr_str_nr(),i.get_addr_zip(),i.get_addr_citytown_nr(),
				i.get_addr_is_valid(),i.get_citizenship(),i.get_phone_1_code(),
				i.get_phone_1_nr(),i.get_phone_2_code(),i.get_phone_2_nr(),
				i.get_cellphone_1_nr(),i.get_cellphone_2_nr(),i.get_fax(),
				i.get_email(),i.get_civil_status(),i.get_sex(),i.get_title(),
				i.get_photo(),i.get_photo_filename(),i.get_ethnic_orig(),
				i.get_org_id(),i.get_sss_nr(),i.get_nat_id_nr(),i.get_religion(),
				i.get_mother_pid(),i.get_father_pid(),i.get_contact_person(),
				i.get_contact_pid(),i.get_contact_relation(),i.get_death_date(),
				i.get_death_encounter_nr(),i.get_death_cause(),i.get_death_cause_code(),
				i.get_date_updade(),i.get_status(),i.get_history(),
				i.get_modify_id(),i.get_modify_time(),i.get_create_id(),
				i.get_create_time(),i.get_relative_name_first(),
				i.get_relative_name_last(),i.get_relative_phone(),
				i.get_birth_country(),i.get_birth_entity(),i.get_birth_town(),
				i.get_birth_city(),i.get_illiterate(),i.get_educational_level(),
				i.get_educational_plan(),i.get_years_approved(),i.get_profession(),
				i.get_occupation(),i.get_sso(),i.get_addr_citytown_name(),
				i.get_addr_locality(),i.get_addr_entity(),i.get_addr_town(),
				i.get_addr_sector(),i.get_addr_house(),i.get_addr_floor(),
				i.get_addr_reference(),i.get_addr_time(),i.get_name_mother(),
				i.get_name_father(),i.get_comments(),i.get_imaging(),
				i.get_cod_estado(),i.get_numero()]

def all_up_message(send,recv):
	if send and recv:
		print "El Sistema se Actualizo con Exito!"
	elif send:
		print "Se enviaron los datos correctamente"
	elif recv:
		print "Se recibieron los datos correctamente"
	else:
		print "No se hizo nada"
		print "Sistema Actualizado"

#def get_regs_info_send(values):
#	list = []
#	for i in values:
#		if i.get_version_actual() > i.get_version_sincronizacion():
#			list.append(i)
#	return list
def sending(ser, list):
	if len(list) > 0:
		ser.send_message("1")
	else:
		ser.send_message("0")

def reciving(ser):
	data = ser.recv_message()
	if data == "0":
		return 0
	else:
		return 1

def recv_client_name(ser):
	return ser.recv_message()

if __name__ == "__main__":
	send_data = False
	recv_data = False
	ssl = SSL()
	context = ssl.context()
	server = Server(context)
	server.conect()
	try:
# Conexion a la base de datos
		bd = BD()
		bd.conect()
# Inicio de la comunicacion
# Paso 1. Se recibe la 
# informacion de versiones
# desde el Servidor
		client_name = recv_client_name(server)	
		print "1"
		if (reciving(server)):
			recv_data = True
			print "reciving info"
			regs_list = server.recv()
			regs_list_person = server.recv_ex()
			regs_list_info_hist = server.recv()
			regs_list_info_soap = server.recv()
			regs_list_info_person = generators_person(regs_list_person)

			# Actualizando registros
			bd.update_person(regs_list_info_person)
			bd.update_clin_hist(regs_list_info_hist,regs_list)
			bd.update_soap(regs_list_info_soap,regs_list)   
			bd.update_sync(regs_list,client_name)

# Obteniendo los registros
# que pertenecen a un 
# Servidor Regional especifico
		
		regs_diff_bd = bd.get_rows_diff(client_name)	
		regs_list_diff_bd = generators_sync(regs_diff_bd)

# Verificando que un mismo
# registro no tenga cambios
# en el Servidor Grid y 
# Servidor Regional
		if recv_data:
			regs_conflict = get_regs_conflict(regs_list,regs_list_diff_bd)
		else:
			regs_conflict = get_regs_conflict([],regs_list_diff_bd)
		sending(server,regs_conflict)
		if len(regs_conflict) > 0:
			print "Existe conflicto entre varios registros"
		else:
			print "No existe conflicto entre los registros"

 # Paso 2. Verificar en mi bd
 # cuales registros tienen cambios
 # y almacenarlos en las listas
 # correspondientes
		sending(server,regs_list_diff_bd)
		if len(regs_list_diff_bd) > 0:
			send_data = True 
			print "sending info"
			regs_info_send_hist = bd.get_regs_info_send_hist(regs_list_diff_bd)
			regs_info_send_soap = bd.get_regs_info_send_soap(regs_list_diff_bd)
			regs_list_info_send_hist = generators_clin_hist(regs_info_send_hist)
			regs_list_info_send_soap = generators_soap(regs_info_send_soap)
			
			server.send(regs_list_diff_bd)
			server.send(regs_list_info_send_hist)
			server.send(regs_list_info_send_soap)

			# Actualizando los registros
			bd.update_sync(regs_list_diff_bd,client_name)
			
		all_up_message(send_data,recv_data)
		bd.close()
		server.close()
		print "Sistema Actualizado con Exito!"
	except KeyboardInterrupt:
		print "Keyboard Interrupt recieved"
		server.close()
